﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WinFormsAppTest.pojo;

namespace WinFormsAppTest.Dao
{
    internal class OuttimeDao
    {
        //使用hyl版本进行登录
        //String connectionString = @"Data Source=DESKTOP-SQERB5L\MSSQLSERVERR;" +
        //    "Initial Catalog=hylpersonsystem;" +
        //    "User ID=hyl;" +
        //    "Password=123456;";

        //使用window登录
        String connectionString = @"Data Source=DESKTOP-SQERB5L\MSSQLSERVERR;" +
            "Initial Catalog=hylpersonsystem;" +
            "Integrated Security=true;";



        //connect 无参
        public SqlConnection connect()
        {

            using (SqlConnection sc = new SqlConnection(connectionString))
            {  ///
                sc.Open();
                try
                {
                    //sc.Open();

                    // 检查连接状态
                    if (sc.State == ConnectionState.Open)
                    {
                        Console.WriteLine("数据库连接成功！");
                        MessageBox.Show("数据库连接成功！");
                        sc.Close();

                        // 连接成功后可以执行其他操作
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("数据库连接失败：" + ex.Message);
                    MessageBox.Show("数据库连接失败！");
                    sc.Close();
                }
                return sc;
            }///
        }
        //


        //添加出差信息
        public void CreateOuttime(DateTime begintime, DateTime endtime,
                               string palce, int  workid, int userid)
        {
            // 在这里执行具体的数据库操作
            // 例如使用 SqlCommand 执行查询语句或执行存储过程
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "insert into outtime values(@Begintime,@Endtime,@Palce,@Workid,@Userid)";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("注册时，数据库连接失败！");
                return;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                command.Parameters.AddWithValue("@Begintime", begintime);
                command.Parameters.AddWithValue("@Endtime", endtime);
                command.Parameters.AddWithValue("@Palce", palce);
                command.Parameters.AddWithValue("@Workid", workid);
                command.Parameters.AddWithValue("@Userid", userid);
                command.ExecuteNonQuery();
                sc.Close();
                return;
            }
        }

        //根据出差id删除出差信息
        public void DeleteOuttimeById(int id)
        {
            // 在这里执行具体的数据库操作
            // 例如使用 SqlCommand 执行查询语句或执行存储过程
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "delete from outtime where outid=@OutId";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("删除时，数据库连接失败！");
                return;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                command.Parameters.AddWithValue("@OutId", id);
                command.ExecuteNonQuery();
                sc.Close();
                return;
            }
        }

        //根据用户id删除出差信息
        public void DeleteOuttimeByUserId(int id)
        {
            // 在这里执行具体的数据库操作
            // 例如使用 SqlCommand 执行查询语句或执行存储过程
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "delete from outtime where userid=@UserId";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("删除时，数据库连接失败！");
                return;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                command.Parameters.AddWithValue("@UserId", id);
                command.ExecuteNonQuery();
                sc.Close();
                return;
            }
        }

        //根据出差id更新出差信息
        public void UpdateOuttimeById(int id,DateTime begintime, DateTime endtime,
                               string palce)
        {
            // 在这里执行具体的数据库操作
            // 例如使用 SqlCommand 执行查询语句或执行存储过程
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "update outtime set begintime=@Begintime,endtime=@Endtime,place=@Palce where outid=@OutId";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("更新时，数据库连接失败！");
                return;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                command.Parameters.AddWithValue("@Begintime", begintime);
                command.Parameters.AddWithValue("@Endtime", endtime);
                command.Parameters.AddWithValue("@Palce", palce);
                command.Parameters.AddWithValue("@OutId", id);
                command.ExecuteNonQuery();//这句话在更新语句中需要存在才能执行更新语句
                sc.Close();
                return;
            }
        }


        //根据用户id获取用户全部人员出差信息
        public List<Outtime> GetOuttimeByUserId(int id)
        {
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "select * from outtime where userid = @Userid";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("打开时，数据库连接失败！");
                return null;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                command.Parameters.AddWithValue("@Userid", id);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    List<Outtime> files = new List<Outtime>();
                    while (reader.Read())
                    {
                        // 处理查询结果
                        // 例如输出数据到控制台或存储到集合中
                        int outid = reader.GetInt32(0);
                        DateTime begintime = reader.GetDateTime(1);
                        DateTime endtime = reader.GetDateTime(2);
                        string place = reader.GetString(3);
                        int workid = reader.GetInt32(4);
                        int userid = reader.GetInt32(5);
                        Outtime f = new Outtime(outid, begintime, endtime, place, workid, userid);
                        files.Add(f);
                    }
                    sc.Close();
                    return files;
                }
            }
        }
        //


        //根据出差id获取用户全部人员出差信息
        public List<Outtime> GetOuttimeById(int id)
        {
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "select * from outtime where outid = @Outid";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("打开时，数据库连接失败！");
                return null;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                command.Parameters.AddWithValue("@Outid", id);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    List<Outtime> files = new List<Outtime>();
                    while (reader.Read())
                    {
                        // 处理查询结果
                        // 例如输出数据到控制台或存储到集合中
                        int outid = reader.GetInt32(0);
                        DateTime begintime = reader.GetDateTime(1);
                        DateTime endtime = reader.GetDateTime(2);
                        string place = reader.GetString(3);
                        int workid = reader.GetInt32(4);
                        int userid = reader.GetInt32(5);
                        Outtime f = new Outtime(outid, begintime, endtime, place, workid, userid);
                        files.Add(f);
                    }
                    sc.Close();
                    return files;
                }
            }
        }
        //

        //根据出差地点获取用户全部人员出差信息
        public List<Outtime> GetOuttimeByPlace(string _place)
        {
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "select * from outtime where place = @Place";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("打开时，数据库连接失败！");
                return null;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                command.Parameters.AddWithValue("@Place", _place);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    List<Outtime> files = new List<Outtime>();
                    while (reader.Read())
                    {
                        // 处理查询结果
                        // 例如输出数据到控制台或存储到集合中
                        int outid = reader.GetInt32(0);
                        DateTime begintime = reader.GetDateTime(1);
                        DateTime endtime = reader.GetDateTime(2);
                        string place = reader.GetString(3);
                        int workid = reader.GetInt32(4);
                        int userid = reader.GetInt32(5);
                        Outtime f = new Outtime(outid, begintime, endtime, place, workid, userid);
                        files.Add(f);
                    }
                    sc.Close();
                    return files;
                }
            }
        }
        //


        //获取用户全部人员出差信息
        public List<Outtime> GetOuttimes()
        {
            SqlConnection sc = new SqlConnection(connectionString);
            sc.Open();
            String sql = "select * from outtime";
            if (sc.State != ConnectionState.Open)
            {
                MessageBox.Show("打开时，数据库连接失败！");
                return null;
            }
            using (SqlCommand command = new SqlCommand(sql, sc))
            {
                //command.Parameters.AddWithValue("@Place", _place);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    List<Outtime> files = new List<Outtime>();
                    while (reader.Read())
                    {
                        // 处理查询结果
                        // 例如输出数据到控制台或存储到集合中
                        int outid = reader.GetInt32(0);
                        DateTime begintime = reader.GetDateTime(1);
                        DateTime endtime = reader.GetDateTime(2);
                        string place = reader.GetString(3);
                        int workid = reader.GetInt32(4);
                        int userid = reader.GetInt32(5);
                        Outtime f = new Outtime(outid, begintime, endtime, place, workid, userid);
                        files.Add(f);
                    }
                    sc.Close();
                    return files;
                }
            }
        }
        //

    }
}
